1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Threading.Tasks;
9 using System.Windows.Forms;
10 using System.Runtime.InteropServices;
11 using Excel = Microsoft.Office.Interop.Excel;
12
13 namespace SoftQuanLyNhaHang.Views
14 {
15
16 public partial class frmQuanLyDonHang_XuatExcel : Form
17 {
18
19 public frmQuanLyDonHang_XuatExcel()
20 {
21 InitializeComponent();
22 }
23
24 //Khai báo 1 hàm delegate
25 public delegate void GetThongTinTuNgay_DenNgay(DateTime tuNgay, DateTime denNgay, bool btnTrangThai = true);
26
27 //Khai báo 1 kiểu hàm delegate
28 public GetThongTinTuNgay_DenNgay ChongNgay_TuNgay_DenNgay;
29
30 int _pageSize = 30;
31 int _pageIndex = 1;
32 int _getSearchIdNhaCungCap = 0;
33 private string _getMaDonHang = string.Empty;
34 private string _getTenDonHang = string.Empty;
35 private string _getTrangThaiDonHang = string.Empty;
36 private int _getNhaCungCapId = 0;
37 private int _getPageSize = 30;
38 private int _getPageIndex = 1;
39 private int _getStt = 0;
40 private int _getTongSoBanGhi = 0;
41
42 public frmQuanLyDonHang_XuatExcel(string maDonHang, string tenDonHang, string trangThaiDonHang, int nhaCungCapId, int pageSize, int pageIndex, int stt, int tongSoBanGhi) : this()
43 {
44 _getMaDonHang = maDonHang;
45 _getTenDonHang =tenDonHang;
46 _getTrangThaiDonHang = trangThaiDonHang;
47 _getNhaCungCapId = nhaCungCapId;
48 _getPageSize = pageSize;
49 _getPageIndex = pageIndex;
50 _getStt = stt;
51 _getTongSoBanGhi = tongSoBanGhi;
52 }
53
54 private void HienThiTrangThaiDonHang_ComboBox()
55 {
56 Dictionary<string, string> comboSource = new Dictionary<string, string>();
57 comboSource.Add("-1", "-- Tất cả đơn hàng --");
58 comboSource.Add("DonHangNoNCC", "Đơn hàng nợ NCC");
59 comboSource.Add("DaThanhToan", "Đơn hàng đã thanh toán");
60 //comboSource.Add("NhapHangMoi", "Đơn hàng nhập mới");
61 comboSource.Add("HuyDonHang", "Đơn hàng đã hủy");
62 comboSource.Add("TamDung", "Đơn hàng tạm dừng");
63
64 cmbTrangThaiDonHang.DataSource = new BindingSource(comboSource, null);
65 cmbTrangThaiDonHang.DisplayMember = "Value";
66 cmbTrangThaiDonHang.ValueMember = "Key";
67 }
68
69 private void frmQuanLyDonHang_XuatExcel_Load(object sender, EventArgs e)
70 {
71 this.HienThiTrangThaiDonHang_ComboBox();
72
73 txtMaDonHang.Text = _getMaDonHang;
74 txtTenDonHang.Text = _getTenDonHang;
75 cmbTrangThaiDonHang.SelectedValue = _getTrangThaiDonHang;
76
77 DataTable dtLoad = new DataTable();
78 dtLoad = GetTable_ExportExcel_Content(cmbTrangThaiDonHang.SelectedValue.ToString(), _getPageSize, _getPageIndex, txtMaDonHang.Text.Trim(), txtTenDonHang.Text.Trim(), _getSearchIdNhaCungCap);
79
80 lblTongSoBanGhiHienThi.Text = CauHinhHeThong.HienThiSoBanGhiTuA_DenB(_getPageSize, _getPageIndex, _getStt.ToString(), _getTongSoBanGhi.ToString());
81 }
82
83 private void btnThucHien_Click(object sender, EventArgs e)
84 {
85 //Tao doi tuong luu tap tin
86 SaveFileDialog fsave = new SaveFileDialog();
87 //chi ra duoi de luu
88 fsave.Filter = "Excel|*.xls;*.xlsx;*.xlsm";
89 fsave.ShowDialog();
90 //xu ly
91 if (fsave.FileName != "")
92 {
93 //tao excel app
94 Excel.Application app = new Excel.Application();
95 //tao 1 workbook
96 object Default = System.Reflection.Missing.Value;
97 Excel.Workbook wb = app.Workbooks.Add(Default);
98
99 //tao sheet
100 Excel.Worksheet sheet = (Excel.Worksheet)wb.Worksheets.get_Item(1);
101 // Excel.Worksheet sheet = wb.Sheets.Add(Default, wb.Sheets[wb.Sheets.Count], 1, Default);
102
103 //thiết lập chiều rộng cho cột
104 sheet.Columns[1].ColumnWidth = 18;
105 sheet.Columns[2].ColumnWidth = 28;
106 sheet.Columns[3].ColumnWidth = 48;
107
108 try
109 {
110 DataTable dtExport = new DataTable();
111 dtExport = GetTable_ExportExcel_Content(cmbTrangThaiDonHang.SelectedValue.ToString(), _pageSize, _pageIndex, txtMaDonHang.Text.Trim(), txtTenDonHang.Text.Trim(), _getSearchIdNhaCungCap);
112
113 int tongColumn = dtExport.Columns.Count;
114
115 //doc du lieu tu listview export ra file excel co dinh dang
116 sheet = wb.ActiveSheet;
117 sheet.Name = "Dữ liệu xuất ra";
118 sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, tongColumn]].Merge();
119 sheet.Cells[1, 1].Value = "Danh sách đơn nhập hàng";
120 //sheet.Cells[1, 1].HorizontalAligment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
121 sheet.Cells[1, 1].Font.Size = 20;
122 //sheet.Cells[1, 1].Borders.Weigth = Excel.XlBorderWeight.xlThin;
123
124 //Ngày thực hiện
125 sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, tongColumn]].Merge();
126 sheet.Cells[2, 1].Value = "Ngày thực hiện " + DateTime.Now.ToString();
127 sheet.Cells[2, 1].Font.Size = 13;
128
129 //hiển thị bản ghi từ ? đến ? trong ? mục
130 //int stt_XuatExcel = ((_pageIndex - 1) * _pageSize);
131 //sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, tongColumn]].Merge();
132 //sheet.Cells[3, 1].Value = CauHinhHeThong.HienThiSoBanGhiTuA_DenB(_pageSize, _pageIndex, stt_XuatExcel.ToString(), _tongSoBanGhi.ToString());
133 //sheet.Cells[3, 1].Font.Size = 13;
134
135 //sinh tieu de cho từng cột
136 DataTable dtHeader = new DataTable();
137 dtHeader = GetTable_ExportExcel_Header();
138 for (int h = 0; h < dtHeader.Columns.Count; h++)
139 {
140 sheet.Cells[5, h + 1] = dtHeader.Rows[0][h].ToString();
141 }
142
143 if (dtExport.Rows.Count > 0)
144 {
145 int hang = 5;
146 int cot = 0;
147 foreach (DataRow item in dtExport.Rows)
148 {
149 hang++;
150 for (cot = 0; cot < dtExport.Columns.Count; cot++)
151 {
152 sheet.Cells[hang + 1, cot + 1] = item[cot].ToString();
153 }
154
155
156 }
157 }
158
159 //ghi lại
160 wb.SaveAs(fsave.FileName, Excel.XlFileFormat.xlWorkbookNormal, Default, Default, Default, Default, Excel.XlSaveAsAccessMode.xlExclusive, Default, Default, Default, Default, Default);
161 wb.Close(true, Default, Default);
162 app.Quit();
163
164 releaseObject(sheet);
165 releaseObject(wb);
166 releaseObject(app);
167
168
169 }
170 catch
171 {
172
173 }
174
175 //finally
176 //{
177 //app.Quit();
178 //wb = null;
179 //}
180 }
181 else
182 {
183
184 }
185 }
186
187 //--http://csharp.net-informations.com/excel/csharp-excel-datagridview.htm
188 private void releaseObject(object obj)
189 {
190 try
191 {
192 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
193 obj = null;
194 }
195 catch (Exception ex)
196 {
197 obj = null;
198 MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
199 }
200 finally
201 {
202 GC.Collect();
203 }
204 }
205
206 static DataTable GetTable_ExportExcel_Header()
207 {
208 DataTable table = new DataTable();
209
210 table.Columns.Add("STT", typeof(string));
211 table.Columns.Add("MaDonHang", typeof(string));
212 table.Columns.Add("TenDonHang", typeof(string));
213 table.Columns.Add("HoVaTen", typeof(string));
214 table.Columns.Add("TongMatHang", typeof(string));
215 table.Columns.Add("TongDonHang", typeof(string));
216 table.Columns.Add("TienNoNCC", typeof(string));
217 table.Columns.Add("NgayNhap", typeof(string));
218 table.Columns.Add("ChiPhiPhatSinh", typeof(string));
219 table.Columns.Add("DonHang", typeof(string));
220 table.Columns.Add("GhiChu", typeof(string));
221
222 //add header
223 table.Rows.Add(
224 "STT",
225 "Mã đơn hàng",
226 "Tên đơn hàng",
227 "Họ và tên",
228 "Tổng mặt hàng",
229 "Tổng đơn hàng",
230 "TK Nợ NCC",
231 "Ngày nhập",
232 "Chi phí phát sinh",
233 "Đơn hàng",
234 "Ghi chú"
235 );
236
237 return table;
238 }
239
240 static DataTable GetTable_ExportExcel_Content(string cmbTrangThaiDonHang, int pageSize, int pageIndex, string txtMaDonHang, string txtTenDonHang, int getSearchIdNhaCungCap)
241 {
242 //
243 // Here we create a DataTable with four columns.
244 //
245 DataTable table = new DataTable();
246 table.Columns.Add("STT", typeof(int));
247 table.Columns.Add("MaDonHang", typeof(string));
248 table.Columns.Add("TenDonHang", typeof(string));
249 table.Columns.Add("HoVaTen", typeof(string));
250 table.Columns.Add("TongMatHang", typeof(string));
251 table.Columns.Add("TongDonHang", typeof(string));
252 table.Columns.Add("TienNoNCC", typeof(string));
253 table.Columns.Add("NgayNhap", typeof(string));
254 table.Columns.Add("ChiPhiPhatSinh", typeof(string));
255 table.Columns.Add("DonHang", typeof(string));
256 table.Columns.Add("GhiChu", typeof(string));
257
258 //
259 // Here we add five DataRows.
260 //
261
262 DataTable dtExport = new DataTable();
263 dtExport = Controllers.NhapHangCtrl.FillDataSet_NhapHang_SearchByTrangThai_Search(cmbTrangThaiDonHang, pageSize, pageIndex, txtMaDonHang, txtTenDonHang, getSearchIdNhaCungCap).Tables[0];
264
265 if (dtExport.Rows.Count > 0)
266 {
267 int stt_Ex = ((pageIndex - 1) * pageSize);
268
269 foreach (DataRow item in dtExport.Rows)
270 {
271 stt_Ex++;
272 table.Rows.Add(
273 stt_Ex,
274 item["IdNhapHang"].ToString(),
275 item["TenDonHang"].ToString(),
276 item["HoVaTen"].ToString(),
277 item["TongMatHang"].ToString(),
278 CauHinhHeThong.Format_HienThiKieuTien_CoDauPhanCachHangNghin(item["TongDonHang"].ToString(), true),
279 0,//CauHinhHeThong.Format_HienThiKieuTien_CoDauPhanCachHangNghin(item["No_NCC"].ToString(), true),
280 CauHinhHeThong.Format_HienThiNgayThangNam(item["NgayTao"].ToString(), true),
281 0,//CauHinhHeThong.Format_HienThiKieuTien_CoDauPhanCachHangNghin(item["ChiPhiThem"].ToString(), true),
282 item["TrangThai"].ToString(),
283 item["GhiChu"].ToString()
284 );
285 }
286 }
287
288 //table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now, "Noida");
289
290 return table;
291 }
292
293 private void btnHienThiDanhSach_Click(object sender, EventArgs e)
294 {
295
296 }
297
298 private void btnHuy_Click(object sender, EventArgs e)
299 {
300 this.Close();
301 }
302
303 private void btnTimKiem_Click(object sender, EventArgs e)
304 {
305
306 if (ChongNgay_TuNgay_DenNgay != null)
307 {
308 //Từ ngày đến ngày, click = true
309 ChongNgay_TuNgay_DenNgay(dateTimePickerTu.Value, dateTimePickerDen.Value, true);
310 }
311
312 this.Close();
313 }
314
315 private void btnTiep_Click(object sender, EventArgs e)
316 {
317
318 }
319
320 private void btnSau_Click(object sender, EventArgs e)
321 {
322
323 }
324 }
325 }
